/*
 * @(#)EmailMessageSenderComparator.java
 *
 * Copyright (c) 2004 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */

package com.dcivision.upgrade.handler;

import java.io.StringWriter;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;

import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.sax.SAXSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;

import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.Utility;
import com.dcivision.upgrade.JDBCHelper;
import com.dcivision.upgrade.UpgradeContext;
import com.dcivision.workflow.bean.WorkflowQueryRecordParser;
import com.dcivision.workflow.core.ParseWorkflowQueryRecordXML;
import com.dcivision.workflow.web.ListWorkflowMonitoringForm;

/**
 * @author      Brook.Huang
 * @company     DCIVision Limited
 * @creation    19/04/2007
 * @version     $Revision: 1.3.2.3 $
 */
public class UpgradeHandler4WorkflowModule319To320 extends UpgradeHandlerBase {

  public UpgradeHandler4WorkflowModule319To320(UpgradeContext upgradeContext) throws Exception {
    super(upgradeContext);
  }

  
  protected void upgradeBegin() throws Exception {
    upgradeContext.logReport("<p><b>START UPGRADE PARADM WORKFLOW MODULE</b></p>\n", null);
    // Add your code here to deal with things in the front of this module's
    // upgrade.
  }

  
  protected void upgradeEnd() throws Exception {
    // (1) Alter functions for WORKFLOW_QUERY_RECORD.
    // General 'WORKFLOW_GROUP_ID'.
    // Reconstruct cotent of field 'XML_QUERY' into new pattern.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    processDBMigrationWithTableWorkflowQueryRecord();

    // (2) Alter functions for WORKFLOW_SCHEDULE_TRIGGERS.
    // General 'START_TIME'.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    processDBMigrationWithTableWorkflowScheduleTriggers();

    // (3) Alter functions for MTM_WF_STEP_USER_ACTOR.
    // Update field 'PERMISSION' of exists record with default value 'RW'.
    // Everyone have 'R' permission of every step in default state.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    processDBMigrationWithTableWorkflowStepUserActor();

    // (4) Alter functions for MTM_WF_STEP_FORM_RECORD.
    // The permission of all the form bind with workflow should be 'READ'.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    processDBMigrationWithTableWorkflowStepFormRecord();

    // (5) Alter functions for MTM_WF_PROGRESS_ACCESS_FORM.
    // Insert into MTM_WF_PROGRESS_ACCESS_FORM for every progress accessed
    // form.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    processDBMigrationWithTableWorkflowProgressAccessForm();

    // (6) Alter functions for MTM_WF_DYNAMIC_USER_ACTOR.
    // Update field 'PERMISSION' of exists record with default value 'RW'.
    // Everyone have 'R' permission of every step in default state.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    processDBMigrationWithTableWorkflowDynamicUserActor();

    // (7) Verifies value in table 'SYS_TABLE_KEY' related table 'WORKFLOW_ACTION'
    // Make sure they are consistent with eatch other.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    verifiesIDValue4TableWorkflowAction();

  }

  ///////////////////////////////////////////////////////////////////////////
  ///
  /// DB Migration.
  ///
  ///////////////////////////////////////////////////////////////////////////

  // ~ Major Part to Migration List ------------------------------------------

  private int verifiesIDValue4TableWorkflowAction() throws Exception {
    upgradeContext.logReport("<p><font color=blue>START VERIFIES ID VALUE FOR TABLE WORKFLOW_ACTION</font><br>\n", null);

    int count = 0;

    Statement queryStat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet queryRs = null;

    queryRs = queryStat.executeQuery("SELECT " + " MAX(ID) \n" + "FROM " + " WORKFLOW_ACTION \n");

    while (!Utility.isEmpty(queryRs) && queryRs.next()) {
      int maxID = queryRs.getInt(1);

      if (maxID <= 0) {
        break;
      }

      count += updateSysTableKey4TableWorkflowAction("WORKFLOW_ACTION", maxID);
    }

    try {
      queryRs.close();
      queryStat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    upgradeContext.logReport("<font color=blue>VERIFIES ID VALUE FOR TABLE WORKFLOW_ACTION SUCCESS " + "(TOTAL UPDATE COUNT: " + count
        + ")</font><br></p>\n", null);

    return count;
  }

  private int processDBMigrationWithTableWorkflowQueryRecord() throws Exception {
    upgradeContext.logReport("<p><font color=blue>START UPGRADE WORKFLOW_QUERY_RECORD</font><br>\n", null);

    int count = 0;

    Statement queryRecordStat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet queryRecordRs = null;

    queryRecordRs = queryRecordStat.executeQuery("SELECT A.ID, A.QUERY_NAME,"
        + "A.QUERY_TYPE, A.XML_QUERY, A.DESCRIPTION FROM WORKFLOW_QUERY_RECORD A");

    ParseWorkflowQueryRecordXML queryRecordParser = new ParseWorkflowQueryRecordXML();

    while (!Utility.isEmpty(queryRecordRs) && queryRecordRs.next()) {
      ListWorkflowMonitoringForm queryRecordForm = new ListWorkflowMonitoringForm();

      setQueryRecordFormData(queryRecordParser.parse((StringBuffer) JDBCHelper.getObjectFromResultSet(queryRecordRs, "XML_QUERY")),
          queryRecordForm);

      queryRecordForm.setQueryID(Integer.toString(queryRecordRs.getInt("ID")));
      queryRecordForm.setQueryName(queryRecordRs.getString("QUERY_NAME"));
      queryRecordForm.setQueryDescription(queryRecordRs.getString("DESCRIPTION"));
      queryRecordForm.setQueryType(queryRecordRs.getString("QUERY_TYPE"));

      int workflowGroupID = 1;
      if(!Utility.isEmpty(queryRecordForm.getWorkflowRecordID())){
        ResultSet wfGroupIDRS = upgradeContext.getUpgradeConnection().createStatement().executeQuery(
            "SELECT WORKFLOW_GROUP_ID FROM WORKFLOW_RECORD WHERE ID = " + Integer.parseInt(queryRecordForm.getWorkflowRecordID()));
        while (wfGroupIDRS.next()) {
          workflowGroupID = wfGroupIDRS.getInt("WORKFLOW_GROUP_ID");
          break;
        }
      }else{
        workflowGroupID=0;
      }
     

      queryRecordForm.setWorkflowGroupID(Integer.toString(workflowGroupID));
      StringBuffer XMLQueryResult = genQueryRecordXML(queryRecordForm);

      count += updateWorkflowQueryRecord(queryRecordRs.getInt("ID"), workflowGroupID, XMLQueryResult);
    }

    try {
      queryRecordRs.close();
      queryRecordStat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    upgradeContext.logReport("<font color=blue>UPGRADE WORKFLOW_QUERY_RECORD SUCCESS (TOTAL UPDATE COUNT: " + count + ")</font><br></p>\n",
        null);

    return count;
  }

  private int processDBMigrationWithTableWorkflowScheduleTriggers() throws Exception {
    upgradeContext.logReport("<p><font color=blue>START UPGRADE WORKFLOW_SCHEDULE_TRIGGERS</font><br>\n", null);

    int count = 0;

    Statement scheduleTriggersStat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet scheduleTriggersRs = null;

    scheduleTriggersRs = scheduleTriggersStat.executeQuery("SELECT ID, SCHEDULE_START FROM WORKFLOW_SCHEDULE_TRIGGERS");

    while (!Utility.isEmpty(scheduleTriggersRs) && scheduleTriggersRs.next()) {
      int startTime = 0;
      Timestamp scheduleStartTime = scheduleTriggersRs.getTimestamp("SCHEDULE_START");
      Calendar startCalendar = Utility.timestampToCalendar(scheduleStartTime);
      int hours = startCalendar.get(Calendar.HOUR_OF_DAY);
      int minutes = startCalendar.get(Calendar.MINUTE);
      if (hours > -1) {
        startTime += hours * 60 * 60 * 1000;
      }
      if (minutes > -1) {
        startTime += minutes * 60 * 1000;
      }

      count += updateWorkflowScheduleTriggers(scheduleTriggersRs.getInt("ID"), startTime);
    }

    try {
      scheduleTriggersRs.close();
      scheduleTriggersStat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    upgradeContext.logReport("<font color=blue>UPGRADE WORKFLOW_SCHEDULE_TRIGGERS SUCCESS (TOTAL UPDATE COUNT: " + count
        + ")</font><br></p>\n", null);

    return (count);
  }

  private int processDBMigrationWithTableWorkflowStepUserActor() throws Exception {
    upgradeContext.logReport("<p><font color=blue>START UPGRADE MTM_WF_STEP_USER_ACTOR</font><br>\n", null);

    int count = 0;

    Statement userActorStat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet userActorRs = null;

    // Phase 1: Update exists record with value 'RW'.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    count += updateWorkflowStepUserActor("RW");

    // Phase 2: Insert permission 'R' of every step for everyone if it is
    // not exists yet.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    boolean isInsertDefaultReadPermission4Everyone = true;

    userActorRs = userActorStat.executeQuery("SELECT COUNT(*) AS COUNT FROM MTM_WF_STEP_USER_ACTOR WHERE ACTOR_TYPE = 'Z' ");

    if (!Utility.isEmpty(userActorRs) && userActorRs.next() && userActorRs.getInt("COUNT") > 0) {
      // Ediction 319 didnt have flag equals 'Z'.
      isInsertDefaultReadPermission4Everyone = false;
    }

    if (isInsertDefaultReadPermission4Everyone) {
      userActorRs = userActorStat
          .executeQuery("SELECT A.ID, A.ACTION_TYPE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE "
              + "FROM WORKFLOW_STEP A " + "WHERE A.RECORD_STATUS = 'A' AND ACTION_TYPE <> 'E' ");

      while (!Utility.isEmpty(userActorRs) && userActorRs.next()) {
        count += insertStepUserActor(userActorRs.getInt("ID"), "E", userActorRs.getInt("CREATOR_ID"), userActorRs.getString("CREATE_DATE"),
            userActorRs.getInt("UPDATER_ID"), userActorRs.getString("UPDATE_DATE"));
      }
    }

    try {
      userActorRs.close();
      userActorStat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    upgradeContext.logReport(
        "<font color=blue>UPGRADE MTM_WF_STEP_USER_ACTOR SUCCESS (TOTAL UPDATE COUNT: " + count + ")</font><br></p>\n", null);

    return (count);
  }

  private int processDBMigrationWithTableWorkflowStepFormRecord() throws Exception {
    upgradeContext.logReport("<p><font color=blue>START UPGRADE MTM_WF_STEP_FORM_RECORD</font><br>\n", null);

    int count = 0;

    Statement stepFormStat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet stepFormRs = null;

    // Phase 1: Insert MTM_WF_STEP_FORM_RECORD for every workflow record
    // start step,
    // set PERMISSION value 'C'.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    stepFormRs = stepFormStat.executeQuery("SELECT A.ID AS FORM_RECORD_ID, D.ID AS PAGE_ID, E.SECTION_ID, F.SUB_SECTION,"
        + "B.WORKFLOW_RECORD_ID, C.ID AS STEP_ID, C.STEP_SEQ, C.RECORD_STATUS,"
        + "C.CREATOR_ID, C.CREATE_DATE, C.UPDATER_ID, C.UPDATE_DATE \n" +

        "FROM FORM_RECORD A, MTM_WF_RECORD_OBJECT_LINK B, WORKFLOW_STEP C,"
        + "FORM_PAGE D, MTM_FORM_PAGE_FORM_SECTION E, FORM_SECTION F \n" +

        "WHERE A.ID = B.OBJECT_ID \n" + "   AND B.WORKFLOW_RECORD_ID = C.WORKFLOW_RECORD_ID \n" + "   AND A.ID = D.FORM_RECORD_ID \n"
        + "   AND D.ID = E.PAGE_ID \n" + "   AND E.SECTION_ID = F.ID \n" + "   AND A.RECORD_STATUS = B.RECORD_STATUS \n"
        + "   AND A.RECORD_STATUS = C.RECORD_STATUS \n" + "   AND A.RECORD_STATUS = D.RECORD_STATUS \n"
        + "   AND A.RECORD_STATUS = E.RECORD_STATUS \n" + "   AND A.RECORD_STATUS = F.RECORD_STATUS \n" + "   AND B.OBJECT_TYPE ='M' \n"
        + "   AND A.RECORD_STATUS = 'A' \n" + "   AND C.STEP_SEQ = 1 \n" + "   AND F.SUB_SECTION = 'N' \n" + "ORDER BY A.ID ");

    while (!Utility.isEmpty(stepFormRs) && stepFormRs.next()) {
      count += insertStepFormRecord(stepFormRs.getInt("STEP_ID"), stepFormRs.getInt("FORM_RECORD_ID"), stepFormRs.getInt("PAGE_ID"),
          stepFormRs.getInt("SECTION_ID"), -1, "C", "Y", stepFormRs.getInt("CREATOR_ID"), stepFormRs.getString("CREATE_DATE"), stepFormRs
              .getInt("UPDATER_ID"), stepFormRs.getString("UPDATE_DATE"));
    }

    // Phase 2: Insert MTM_WF_STEP_FORM_RECORD for every workflow record
    // step that
    // have been seted the permission.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    stepFormRs = stepFormStat.executeQuery("SELECT A.ID, A.FORM_SECTION_ID, A.RECORD_STATUS, "
        + "A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE " +

        "FROM WORKFLOW_STEP A " +

        "WHERE A.RECORD_STATUS = 'A' " + " AND FORM_SECTION_ID IS NOT NULL " + " AND FORM_SECTION_ID <> '0|0' ");

    while (!Utility.isEmpty(stepFormRs) && stepFormRs.next()) {
      String formSectStr = stepFormRs.getString("FORM_SECTION_ID");

      String[] changeIDArr = formSectStr.substring(0, formSectStr.indexOf("|")).split(",");
      String[] readIDArr = formSectStr.substring(formSectStr.indexOf("|") + 1).split(",");

      for (int i = 0; i < changeIDArr.length; i++) {
        if (!"0".equals(changeIDArr[i])) {
          count += insertStepFormRecordByPermissionSectionID(Integer.parseInt(changeIDArr[i]), "C", stepFormRs.getInt("ID"), stepFormRs
              .getInt("CREATOR_ID"), stepFormRs.getString("CREATE_DATE"), stepFormRs.getInt("UPDATER_ID"), stepFormRs
              .getString("UPDATE_DATE"));
        }
      }

      for (int i = 0; i < readIDArr.length; i++) {
        if (!"0".equals(readIDArr[i])) {
          count += insertStepFormRecordByPermissionSectionID(Integer.parseInt(readIDArr[i]), "R", stepFormRs.getInt("ID"), stepFormRs
              .getInt("CREATOR_ID"), stepFormRs.getString("CREATE_DATE"), stepFormRs.getInt("UPDATER_ID"), stepFormRs
              .getString("UPDATE_DATE"));
        }
      }
    }

    // Phase 3: Insert MTM_WF_STEP_FORM_RECORD for every workflow record
    // step whose
    // permission never have be set, default value is 'R'.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    stepFormRs = stepFormStat.executeQuery("SELECT A.ID AS FORM_RECORD_ID, D.ID AS PAGE_ID, E.SECTION_ID, F.SUB_SECTION, "
        + " H.ID AS ELEMENT_ID, B.WORKFLOW_RECORD_ID, C.ID AS STEP_ID, C.STEP_SEQ, "
        + " C.RECORD_STATUS, C.CREATOR_ID, C.CREATE_DATE, C.UPDATER_ID, C.UPDATE_DATE \n" +

        "FROM FORM_RECORD A, MTM_WF_RECORD_OBJECT_LINK B, WORKFLOW_STEP C,"
        + " FORM_PAGE D, MTM_FORM_PAGE_FORM_SECTION E, FORM_SECTION F, MTM_FORM_SECTION_FORM_ELEMENT G, FORM_ELEMENT H \n" +

        "WHERE A.ID = B.OBJECT_ID \n" + "    AND B.WORKFLOW_RECORD_ID = C.WORKFLOW_RECORD_ID \n" + "    AND A.ID = D.FORM_RECORD_ID \n"
        + "    AND D.ID = E.PAGE_ID \n" + "    AND E.SECTION_ID = F.ID \n" + "    AND F.ID = G.SECTION_ID \n"
        + "    AND G.ELEMENT_ID = H.ID \n" + "    AND A.RECORD_STATUS = B.RECORD_STATUS \n"
        + "    AND A.RECORD_STATUS = C.RECORD_STATUS \n" + "    AND A.RECORD_STATUS = D.RECORD_STATUS \n"
        + "    AND A.RECORD_STATUS = E.RECORD_STATUS \n" + "    AND A.RECORD_STATUS = F.RECORD_STATUS \n"
        + "    AND A.RECORD_STATUS = G.RECORD_STATUS \n" + "    AND A.RECORD_STATUS = H.RECORD_STATUS \n" + "    AND B.OBJECT_TYPE ='M' \n"
        + "    AND A.RECORD_STATUS = 'A' \n" + "    AND C.STEP_SEQ <> 1 \n" + "    AND C.FORM_SECTION_ID = '0|0' \n" + "ORDER BY A.ID ");

    while (!Utility.isEmpty(stepFormRs) && stepFormRs.next()) {
      count += insertStepFormRecord(stepFormRs.getInt("STEP_ID"), stepFormRs.getInt("FORM_RECORD_ID"), stepFormRs.getInt("PAGE_ID"),
          stepFormRs.getInt("SECTION_ID"), stepFormRs.getInt("ELEMENT_ID"), "R", "N", stepFormRs.getInt("CREATOR_ID"), stepFormRs
              .getString("CREATE_DATE"), stepFormRs.getInt("UPDATER_ID"), stepFormRs.getString("UPDATE_DATE"));
    }

    try {
      stepFormRs.close();
      stepFormStat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    upgradeContext.logReport("<font color=blue>UPGRADE MTM_WF_STEP_FORM_RECORD SUCCESS (TOTAL UPDATE COUNT: " + count
        + ")</font><br></p>\n", null);

    return (count);
  }

  private int processDBMigrationWithTableWorkflowProgressAccessForm() throws Exception {
    upgradeContext.logReport("<p><font color=blue>START UPGRADE MTM_WF_PROGRESS_ACCESS_FORM</font><br>\n", null);

    int count = 0;

    Statement scheduleTriggersStat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet scheduleTriggersRs = null;

    scheduleTriggersRs = scheduleTriggersStat
        .executeQuery("SELECT MAX(A.SUBMISSION_ID) SUBMISSION_ID, MAX(A.WORKFLOW_PROGRESS_ID) WORKFLOW_PROGRESS_ID, MAX(A.CREATOR_ID) CREATOR_ID, "
            + " MAX(A.CREATE_DATE) CREATE_DATE, MAX(A.UPDATER_ID) UPDATER_ID, MAX(A.UPDATE_DATE) UPDATE_DATE, MAX(B.FORM_RECORD_ID) FORM_RECORD_ID \n"
            + "FROM FORM_DATA_ELEMENT A, FORM_SUBMISSION B \n"
            + "WHERE A.SUBMISSION_ID = B.ID \n"
            + "   AND A.WORKFLOW_PROGRESS_ID IS NOT NULL \n" + "GROUP BY A.WORKFLOW_PROGRESS_ID");

    while (!Utility.isEmpty(scheduleTriggersRs) && scheduleTriggersRs.next()) {
      count += insertWorkflowProgressAccessForm(scheduleTriggersRs.getInt("WORKFLOW_PROGRESS_ID"), scheduleTriggersRs
          .getInt("FORM_RECORD_ID"), scheduleTriggersRs.getInt("SUBMISSION_ID"), "C", "A", scheduleTriggersRs.getInt("CREATOR_ID"),
          scheduleTriggersRs.getTimestamp("CREATE_DATE"), scheduleTriggersRs.getInt("UPDATER_ID"), scheduleTriggersRs
              .getTimestamp("UPDATE_DATE"));
    }

    try {
      scheduleTriggersRs.close();
      scheduleTriggersStat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    upgradeContext.logReport("<font color=blue>UPGRADE MTM_WF_PROGRESS_ACCESS_FORM SUCCESS (TOTAL UPDATE COUNT: " + count
        + ")</font><br></p>\n", null);

    return (count);
  }

  private int processDBMigrationWithTableWorkflowDynamicUserActor() throws Exception {
    upgradeContext.logReport("<p><font color=blue>START UPGRADE MTM_WF_DYNAMIC_USER_ACTOR</font><br>\n", null);

    int count = 0;

    // Update exists record with value 'RW'.
    count += updateWorkflowDynamicUserActor("RW");

    upgradeContext.logReport("<font color=blue>UPGRADE MTM_WF_DYNAMIC_USER_ACTOR SUCCESS (TOTAL UPDATE COUNT: " + count
        + ")</font><br></p>\n", null);

    return (count);
  }

  // ~ Detail Operation ------------------------------------------------------

  private int insertWorkflowProgressAccessForm(int WORKFLOW_PROGRESS_ID, int FORM_RECORD_ID, int FORM_SUBMISSION_ID, String PROCESSED,
      String RECORD_STATUS, int CREATOR_ID, Timestamp CREATE_DATE, int UPDATER_ID, Timestamp UPDATE_DATE) throws Exception {
    int count = 0;

    PreparedStatement stat = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("INSERT INTO MTM_WF_PROGRESS_ACCESS_FORM \n"
        + "(ID, WORKFLOW_PROGRESS_ID, FORM_RECORD_ID, FORM_SUBMISSION_ID, PROCESSED, "
        + "RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) \n" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString());

    int queryIndex = 1;
    int ID = DataSourceFactory.getNextSequence("MTM_WF_PROGRESS_ACCESS_FORM").intValue();

    stat.setInt(queryIndex++, ID);
    stat.setInt(queryIndex++, WORKFLOW_PROGRESS_ID);
    stat.setInt(queryIndex++, FORM_RECORD_ID);
    stat.setInt(queryIndex++, FORM_SUBMISSION_ID);
    stat.setString(queryIndex++, PROCESSED);
    stat.setString(queryIndex++, RECORD_STATUS);
    stat.setInt(queryIndex++, 0);
    stat.setInt(queryIndex++, CREATOR_ID);
    stat.setTimestamp(queryIndex++, CREATE_DATE);
    stat.setInt(queryIndex++, UPDATER_ID);
    stat.setTimestamp(queryIndex++, UPDATE_DATE);

    count = stat.executeUpdate();

    if (count > 0) {
      upgradeContext.logReport(null, "Insert(ID:    " + ID + ")    <br>\n");
    }

    try {
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return (count);
  }

  private int insertStepUserActor(int WORKFLOW_STEP_ID, String ACTION_TYPE, int CREATOR_ID, String CREATE_DATE, int UPDATER_ID,
      String UPDATE_DATE) throws Exception {
    int count = 0;

    PreparedStatement stat = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat
        .append("INSERT INTO MTM_WF_STEP_USER_ACTOR \n"
            + "(ID, WORKFLOW_STEP_ID, ACTOR_TYPE, ACTOR_ID, PERMISSION, ACTION_TYPE, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString());

    int queryIndex = 1;
    int ID = DataSourceFactory.getNextSequence("MTM_WF_STEP_USER_ACTOR").intValue();

    stat.setInt(queryIndex++, ID);
    stat.setInt(queryIndex++, WORKFLOW_STEP_ID);
    stat.setString(queryIndex++, "Z");
    stat.setInt(queryIndex++, 0);
    stat.setString(queryIndex++, "R");
    stat.setString(queryIndex++, ACTION_TYPE);
    stat.setString(queryIndex++, "A");
    stat.setInt(queryIndex++, 1);
    stat.setInt(queryIndex++, CREATOR_ID);
    stat.setString(queryIndex++, CREATE_DATE);
    stat.setInt(queryIndex++, UPDATER_ID);
    stat.setString(queryIndex++, UPDATE_DATE);

    count = stat.executeUpdate();

    if (count > 0) {
      upgradeContext.logReport(null, "Insert(ID:    " + ID + ")    <br>\n");
    }

    try {
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return (count);
  }

  private int insertStepFormRecordByPermissionSectionID(int FORM_SECTION_ID, String PERMISSION, int WORKFLOW_STEP_ID, int CREATOR_ID,
      String CREATE_DATE, int UPDATER_ID, String UPDATE_DATE) throws Exception {
    int count = 0;

    Statement stat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet rs = null;

    rs = stat.executeQuery("SELECT A.ID AS FORM_RECORD_ID, B.ID AS FORM_PAGE_ID, C.ID AS FORM_SECTION_ID," + "C.SUB_SECTION \n" +

    "FROM FORM_RECORD A, FORM_PAGE B, FORM_SECTION C, MTM_FORM_PAGE_FORM_SECTION D \n" +

    "WHERE A.ID = B.FORM_RECORD_ID \n" + "   AND B.ID = D.PAGE_ID \n" + "   AND D.SECTION_ID = C.ID \n"
        + "   AND A.RECORD_STATUS = B.RECORD_STATUS \n" + "   AND A.RECORD_STATUS = C.RECORD_STATUS \n"
        + "   AND A.RECORD_STATUS = D.RECORD_STATUS \n" + "   AND C.ID = " + FORM_SECTION_ID);

    while (!Utility.isEmpty(rs) && rs.next()) {
      count += insertStepFormRecord(WORKFLOW_STEP_ID, rs.getInt("FORM_RECORD_ID"), rs.getInt("FORM_PAGE_ID"), rs.getInt("FORM_SECTION_ID"),
          -1, PERMISSION, "N", CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE);
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return (count);
  }

  private int insertStepFormRecord(int WORKFLOW_STEP_ID, int FORM_RECORD_ID, int FORM_PAGE_ID, int FORM_SECTION_ID, int FORM_ELEMENT_ID,
      String PERMISSION, String MANDATORY, int CREATOR_ID, String CREATE_DATE, int UPDATER_ID, String UPDATE_DATE) throws Exception {
    int count = 0;

    PreparedStatement stat = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat
        .append("INSERT INTO MTM_WF_STEP_FORM_RECORD "
            + "(ID, WORKFLOW_STEP_ID, FORM_RECORD_ID, FORM_PAGE_ID, FORM_SECTION_ID, FORM_ELEMENT_ID, PERMISSION, MANDATORY, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString());

    int queryIndex = 1;
    int ID = DataSourceFactory.getNextSequence("MTM_WF_STEP_FORM_RECORD").intValue();

    stat.setInt(queryIndex++, ID);
    stat.setInt(queryIndex++, WORKFLOW_STEP_ID);
    stat.setInt(queryIndex++, FORM_RECORD_ID);

    if (FORM_PAGE_ID == -1) {
      stat.setNull(queryIndex++, java.sql.Types.VARCHAR);
    } else {
      stat.setInt(queryIndex++, FORM_PAGE_ID);
    }

    if (FORM_SECTION_ID == -1) {
      stat.setNull(queryIndex++, java.sql.Types.VARCHAR);
    } else {
      stat.setInt(queryIndex++, FORM_SECTION_ID);
    }

    if (FORM_ELEMENT_ID == -1) {
      stat.setNull(queryIndex++, java.sql.Types.VARCHAR);
    } else {
      stat.setInt(queryIndex++, FORM_ELEMENT_ID);
    }

    stat.setString(queryIndex++, PERMISSION);
    stat.setString(queryIndex++, MANDATORY);
    stat.setString(queryIndex++, "A");
    stat.setInt(queryIndex++, 1);
    stat.setInt(queryIndex++, CREATOR_ID);
    stat.setString(queryIndex++, CREATE_DATE);
    stat.setInt(queryIndex++, UPDATER_ID);
    stat.setString(queryIndex++, UPDATE_DATE);

    count = stat.executeUpdate();

    if (count > 0) {
      upgradeContext.logReport(null, "Insert(ID:    " + ID + ")    <br>\n");
    }

    try {
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return (count);
  }

  private int updateWorkflowScheduleTriggers(int ID, int START_TIME) throws Exception {
    int count = 0;

    PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("SELECT ID, START_TIME \n" + "FROM WORKFLOW_SCHEDULE_TRIGGERS \n" + "WHERE ID = ? \n");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);
    stat.setInt(1, ID);

    rs = stat.executeQuery();
    while (!Utility.isEmpty(rs) && rs.next()) {
      count++;
      upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
      upgradeContext.logReport(null, "START_TIME:    ");
      upgradeContext.logReport(null, "from    " + rs.getInt("START_TIME") + ":    ");
      rs.updateInt("START_TIME", START_TIME);
      rs.updateRow();
      upgradeContext.logReport(null, "to    " + rs.getInt("START_TIME") + ":    <br>\n");
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return (count);
  }

  private int updateWorkflowDynamicUserActor(String defaultPermission) throws Exception {
    int count = 0;

    PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("SELECT ID, PERMISSION \n" + "FROM MTM_WF_DYNAMIC_USER_ACTOR \n");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);

    rs = stat.executeQuery();
    while (!Utility.isEmpty(rs) && rs.next()) {
      count++;
      upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
      upgradeContext.logReport(null, "PERMISSION:    ");
      upgradeContext.logReport(null, "from    " + rs.getString("PERMISSION") + ":    ");
      rs.updateString("PERMISSION", defaultPermission);
      rs.updateRow();
      upgradeContext.logReport(null, "to    " + rs.getString("PERMISSION") + ":    <br>\n");
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return (count);
  }

  private int updateWorkflowStepUserActor(String defaultPermission) throws Exception {
    int count = 0;

    PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("SELECT ID, PERMISSION \n" + "FROM MTM_WF_STEP_USER_ACTOR \n");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);

    rs = stat.executeQuery();
    while (!Utility.isEmpty(rs) && rs.next()) {
      count++;
      upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
      upgradeContext.logReport(null, "PERMISSION:    ");
      upgradeContext.logReport(null, "from    " + rs.getString("PERMISSION") + ":    ");
      rs.updateString("PERMISSION", defaultPermission);
      rs.updateRow();
      upgradeContext.logReport(null, "to    " + rs.getString("PERMISSION") + ":    <br>\n");
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return (count);
  }

  private int updateSysTableKey4TableWorkflowAction(String tableName, int maxID) throws Exception {
    int count = 0;

    PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("SELECT " + " TABLE_NAME, TABLE_KEY_MAX \n" + "FROM " + " SYS_TABLE_KEY \n" + "WHERE " + " TABLE_NAME = '" + tableName
        + "' \n");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);

    rs = stat.executeQuery();
    while (!Utility.isEmpty(rs) && rs.next()) {
      upgradeContext.logReport(null, "Update(TABLE_NAME:    " + rs.getString("TABLE_NAME") + ")    ");
      upgradeContext.logReport(null, "TABLE_KEY_MAX:    ");
      upgradeContext.logReport(null, "from    " + rs.getInt("TABLE_KEY_MAX") + ":    ");
      rs.updateInt("TABLE_KEY_MAX", maxID);
      rs.updateRow();
      upgradeContext.logReport(null, "to    " + rs.getInt("TABLE_KEY_MAX") + ":    <br>\n");
      count++;
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return count;
  }

  private int updateWorkflowQueryRecord(int ID, int WORKFLOW_GROUP_ID, StringBuffer XML_QUERY) throws Exception {
    int count = 0;

    PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("SELECT ID, WORKFLOW_GROUP_ID, XML_QUERY \n" + "FROM WORKFLOW_QUERY_RECORD \n" + "WHERE ID = ? \n");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);
    stat.setInt(1, ID);

    rs = stat.executeQuery();
    while (!Utility.isEmpty(rs) && rs.next()) {
      count++;
      upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
      upgradeContext.logReport(null, "WORKFLOW_GROUP_ID:    XML_QUERY:    ");
      upgradeContext.logReport(null, "from    " + rs.getInt("WORKFLOW_GROUP_ID") + ":    "
          + JDBCHelper.getObjectFromResultSet(rs, "XML_QUERY") + ":    ");
      if(WORKFLOW_GROUP_ID!=0){
        rs.updateInt("WORKFLOW_GROUP_ID", WORKFLOW_GROUP_ID);
      }
      rs.updateBytes("XML_QUERY", JDBCHelper.object2Bytes(XML_QUERY));
      rs.updateRow();
      upgradeContext.logReport(null, "to    " + rs.getInt("WORKFLOW_GROUP_ID") + ":    "
          + JDBCHelper.getObjectFromResultSet(rs, "XML_QUERY") + ":    <br>\n");
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return (count);
  }

  // ~ Private Helper Operation
  // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  private void setQueryRecordFormData(Document queryRecordDoc, ListWorkflowMonitoringForm queryRecordForm) {

    NodeList taskTypeList = queryRecordDoc.getElementsByTagName("taskType");
    if (taskTypeList.getLength() > 0) {
      Node taskTypeN = taskTypeList.item(0).getFirstChild();
      String taskType = "";
      if (taskTypeN != null) {
        taskType = taskTypeN.getNodeValue();
      }

      queryRecordForm.setTaskType(taskType);
    }
    NodeList actionTakerList = queryRecordDoc.getElementsByTagName("actionTaker");
    if (actionTakerList.getLength() > 0) {
      Node actionTakerN = actionTakerList.item(0).getFirstChild();
      String actionTaker = "";
      if (actionTakerN != null) {
        actionTaker = actionTakerN.getNodeValue();
      }

      queryRecordForm.setActionTaker(actionTaker);
    }

    NodeList priorityList = queryRecordDoc.getElementsByTagName("priority");
    if (priorityList.getLength() > 0) {
      Node priorityN = priorityList.item(0).getFirstChild();
      String priority = "";
      if (priorityN != null) {
        priority = priorityN.getNodeValue();
      }

      queryRecordForm.setPriority(priority);
    }

    NodeList queryDateList = queryRecordDoc.getElementsByTagName("Query_date");
    if (queryDateList.getLength() > 0) {
      Element queryDateNode = (Element) queryDateList.item(0);

      NodeList queryDateSubList = queryDateNode.getElementsByTagName("from");
      NodeList queryDateToSubList = queryDateNode.getElementsByTagName("to");
      String queryDateFromValue = "";
      String queryDateToValue = "";
      if (queryDateSubList.item(0).getFirstChild() != null) {
        queryDateFromValue = queryDateSubList.item(0).getFirstChild().getNodeValue();
      }
      if (queryDateToSubList.item(0).getFirstChild() != null) {
        queryDateToValue = queryDateToSubList.item(0).getFirstChild().getNodeValue();
      }
      queryRecordForm.setDatePeriodFrom(queryDateFromValue);
      queryRecordForm.setDatePeriodTo(queryDateToValue);
    }

    NodeList statusList = queryRecordDoc.getElementsByTagName("status");
    if (statusList.getLength() > 0) {
      Node statusN = statusList.item(0).getFirstChild();
      String status = "";
      if (statusN != null) {
        status = statusN.getNodeValue();
      }

      queryRecordForm.setStatus(status);
    }
    NodeList workflowCategoryIDList = queryRecordDoc.getElementsByTagName("workflowCategoryID");
    if (workflowCategoryIDList.getLength() > 0) {
      Node workflowCategoryIDN = workflowCategoryIDList.item(0).getFirstChild();
      String workflowCategoryID = "";
      if (workflowCategoryIDN != null) {
        workflowCategoryID = workflowCategoryIDN.getNodeValue();
      }

      queryRecordForm.setWorkflowCategoryID(workflowCategoryID);
    }
    NodeList submitterList = queryRecordDoc.getElementsByTagName("submitter");
    if (submitterList.getLength() > 0) {
      Node submitterN = submitterList.item(0).getFirstChild();
      String submitter = "";
      if (submitterN != null) {
        submitter = submitterN.getNodeValue();
      }

      queryRecordForm.setSubmitter(submitter);
    }
    NodeList workflowRecordIDList = queryRecordDoc.getElementsByTagName("workflowRecordID");
    if (workflowRecordIDList.getLength() > 0) {
      Node workflowRecordIDN = workflowRecordIDList.item(0).getFirstChild();
      String workflowRecordID = "";
      if (workflowRecordIDN != null) {
        workflowRecordID = workflowRecordIDN.getNodeValue();
      }
      queryRecordForm.setWorkflowRecordID(workflowRecordID);
    }
    NodeList tractIDList = queryRecordDoc.getElementsByTagName("tractID");
    if (tractIDList.getLength() > 0) {
      Node tractIDN = tractIDList.item(0).getFirstChild();
      String tractID = "";
      if (tractIDN != null) {
        tractID = tractIDN.getNodeValue();
      }

      queryRecordForm.setTrackID(tractID);
    }
    NodeList stepNameList = queryRecordDoc.getElementsByTagName("stepName");
    if (stepNameList.getLength() > 0) {
      Node stepNameN = stepNameList.item(0).getFirstChild();
      String stepName = "";
      if (stepNameN != null) {
        stepName = stepNameN.getNodeValue();
      }
      queryRecordForm.setStepName(stepName);
    }

    NodeList stageNameList = queryRecordDoc.getElementsByTagName("stageName");
    if (stageNameList.getLength() > 0) {
      Node stageNameN = stageNameList.item(0).getFirstChild();
      String stageName = "";
      if (stageNameN != null) {
        stageName = stageNameN.getNodeValue();
      }
      queryRecordForm.setStageName(stageName);
    }

    NodeList relatedObjectList = queryRecordDoc.getElementsByTagName("relatedObject");
    if (relatedObjectList.getLength() > 0) {
      Node relatedObjectN = relatedObjectList.item(0).getFirstChild();
      String relatedObject = "";
      if (relatedObjectN != null) {
        relatedObject = relatedObjectN.getNodeValue();
      }

      queryRecordForm.setRelatedObject(relatedObject);
    }
    NodeList descriptionList = queryRecordDoc.getElementsByTagName("description");
    if (descriptionList.getLength() > 0) {
      Node descriptionN = descriptionList.item(0).getFirstChild();
      String description = "";
      if (descriptionN != null) {
        description = descriptionN.getNodeValue();
      }

      queryRecordForm.setWorkflowDescription(description);
    }
    NodeList periodTimesList = queryRecordDoc.getElementsByTagName("periodTimes");
    if (periodTimesList.getLength() > 0) {
      Node periodTimesN = periodTimesList.item(0).getFirstChild();
      String periodTimes = "";
      if (periodTimesN != null) {
        periodTimes = periodTimesN.getNodeValue();
      }
      queryRecordForm.setPeriodTimes(periodTimes);
    }
    NodeList periodTypeList = queryRecordDoc.getElementsByTagName("periodType");
    if (periodTypeList.getLength() > 0) {
      Node periodTypeN = periodTypeList.item(0).getFirstChild();
      String periodType = "";
      if (periodTypeN != null) {
        periodType = periodTypeN.getNodeValue();
      }

      queryRecordForm.setPeriodType(periodType);
    }
    NodeList periodTimeTypeList = queryRecordDoc.getElementsByTagName("periodTimeType");
    if (periodTimeTypeList.getLength() > 0) {
      Node periodTimeTypeN = periodTimeTypeList.item(0).getFirstChild();
      String periodTimeType = "";
      if (periodTimeTypeN != null) {
        periodTimeType = periodTimeTypeN.getNodeValue();
      }

      queryRecordForm.setPeriodTimeType(periodTimeType);
    }
    NodeList taskCommentList = queryRecordDoc.getElementsByTagName("taskComment");
    if (taskCommentList.getLength() > 0) {
      Node taskCommentN = taskCommentList.item(0).getFirstChild();
      String taskComment = "";
      if (taskCommentN != null) {
        taskComment = taskCommentN.getNodeValue();
      }

      queryRecordForm.setTaskComment(taskComment);
    }
    NodeList attachmentNameList = queryRecordDoc.getElementsByTagName("attachmentName");
    if (attachmentNameList.getLength() > 0) {
      Node attachmentNameN = attachmentNameList.item(0).getFirstChild();
      String attachmentName = "";
      if (attachmentNameN != null) {
        attachmentName = attachmentNameN.getNodeValue();
      }
      queryRecordForm.setAttachmentName(attachmentName);
    }

    NodeList workflowGroupIDList = queryRecordDoc.getElementsByTagName("workflowGroupID");
    if (workflowGroupIDList.getLength() > 0) {
      Node workfowGroupIDN = workflowGroupIDList.item(0).getFirstChild();
      String workflowGroupID = "";
      if (workfowGroupIDN != null) {
        workflowGroupID = workfowGroupIDN.getNodeValue();
      }
      queryRecordForm.setWorkflowGroupID(workflowGroupID);
    }
  }

  private StringBuffer genQueryRecordXML(ListWorkflowMonitoringForm queryRecordForm) throws Exception {
    WorkflowQueryRecordParser queryRecordParser = new WorkflowQueryRecordParser();

    queryRecordParser.setTaskType(queryRecordForm.getTaskType());
    queryRecordParser.setPriority(queryRecordForm.getPriority());
    queryRecordParser.setDatePeriodFrom(queryRecordForm.getDatePeriodFrom());
    queryRecordParser.setDatePeriodTo(queryRecordForm.getDatePeriodTo());
    queryRecordParser.setStatus(queryRecordForm.getStatus());
    queryRecordParser.setWorkflowCategoryID(queryRecordForm.getWorkflowCategoryID());
    queryRecordParser.setWorkflowRecordID(queryRecordForm.getWorkflowRecordID());
    queryRecordParser.setActionTaker(queryRecordForm.getActionTaker());
    queryRecordParser.setTrackID(queryRecordForm.getTrackID());
    queryRecordParser.setStepName(queryRecordForm.getStepName());
    queryRecordParser.setRelatedObject(queryRecordForm.getRelatedObject());
    queryRecordParser.setWorkflowDescription(queryRecordForm.getWorkflowDescription());
    queryRecordParser.setSubmitter(queryRecordForm.getSubmitter());
    queryRecordParser.setPeriodTimes(queryRecordForm.getPeriodTimes());
    queryRecordParser.setPeriodTimeType(queryRecordForm.getPeriodTimeType());
    queryRecordParser.setPeriodType(queryRecordForm.getPeriodType());
    queryRecordParser.setStageName(queryRecordForm.getStageName());
    queryRecordParser.setTaskComment(queryRecordForm.getTaskComment());
    queryRecordParser.setAttachmentName(queryRecordForm.getAttachmentName());
    queryRecordParser.setWorkflowGroupID(queryRecordForm.getWorkflowGroupID());

    // Use a Transformer for output
    TransformerFactory tFactory = TransformerFactory.newInstance();
    Transformer transformer = tFactory.newTransformer();
    // Use the parser as a SAX source for input
    InputSource inputSource = new InputSource(); // no need to take input
    // xml
    // file
    SAXSource source = new SAXSource(queryRecordParser, inputSource);
    StringWriter writer = new StringWriter();
    StreamResult result = new StreamResult(writer);
    transformer.transform(source, result);
    StringBuffer writerBuffer = new StringBuffer();
    writerBuffer.append(writer.toString());
    return (writerBuffer);
  }

}
